package com.example.demomk.Dao;

import androidx.lifecycle.LiveData;
import androidx.room.Dao;
import androidx.room.Insert;
import androidx.room.Query;
import androidx.room.Transaction;
import androidx.room.Update;

import com.example.demomk.entity.AllCount;
import com.example.demomk.entity.AvgMeasurement;
import com.example.demomk.entity.AvgMeasurementWithDetails;
import com.example.demomk.entity.AvgMeasurementWithMeasurements;
import com.example.demomk.entity.MeasurementSummary;

import java.util.List;

@Dao
public interface AvgMeasurementDao {
    @Insert
    long insertAvgMeasurement(AvgMeasurement avgMesurement);
    @Update
    void updateAvgMeasurement(AvgMeasurement avgMesurement);
    @Query("SELECT * FROM avg_measurements WHERE id = :id")
    LiveData<List<AvgMeasurement>>  getAvgMeasurementById(int id);

    //去除livedata方法


    @Query("SELECT a.id, a.user_id, a.location_id, a.start_time, a.flow ," +
            "       u.user_name AS user_name, u.is_active AS is_active , l.location_name AS location_name, l.group_name AS group_name " +
            "FROM avg_measurements a " +
            "JOIN users u ON a.user_id = u.id " +
            "JOIN locations l ON a.location_id = l.id " +
            "ORDER BY a.id DESC")
    LiveData<List<AvgMeasurementWithDetails>> getAllAvgMeasurements();

    //测试分页技术
    @Query("SELECT a.id, a.user_id, a.location_id, a.start_time, a.flow ," +
            "       u.user_name AS user_name, u.is_active AS is_active , l.location_name AS location_name, l.group_name AS group_name " +
            "FROM avg_measurements a " +
            "JOIN users u ON a.user_id = u.id " +
            "JOIN locations l ON a.location_id = l.id " +
            "WHERE a.id > :lastId " +
            "ORDER BY a.id " +
            "LIMIT :limit ")
    LiveData<List<AvgMeasurementWithDetails>> getAllAvgNext(long lastId, int limit);

    @Query("SELECT a.id, a.user_id, a.location_id, a.start_time,a.flow , " +
            "       u.user_name AS user_name,  u.is_active AS is_active, l.location_name AS location_name, l.group_name AS group_name " +
            "FROM avg_measurements a " +
            "JOIN users u ON a.user_id = u.id " +
            "JOIN locations l ON a.location_id = l.id " +
            "WHERE a.id = :id " +
            "ORDER BY a.id DESC")
    LiveData<AvgMeasurementWithDetails> getAllAvgMeasurementId(int id);


    @Query("SELECT a.id, a.user_id, a.location_id, a.start_time, a.flow, " +
            "s.value_05, s.value_10, s.value_20, s.value_30, s.notes, " +  // 假设这些字段在 measurement_details 表
            "u.user_name AS user_name, l.location_name AS location_name, l.group_name AS group_name " +
            "FROM avg_measurements a " +
            "JOIN users u ON a.user_id = u.id " +
            "JOIN locations l ON a.location_id = l.id " +
            "LEFT JOIN measurements s ON a.id = s.id " +  // 假设通过 measurement_id 关联
            "ORDER BY a.id DESC")
    LiveData<List<AllCount>> getAllAvg();

    //校验avg用户是否存在
    @Query("SELECT COUNT(*) FROM avg_measurements WHERE user_id = :userId AND location_id = :locationId")
    LiveData<Integer> isExist(int userId, int locationId);


    @Transaction
    @Query("SELECT * FROM avg_measurements WHERE id = :avgId")
    LiveData<AvgMeasurementWithMeasurements> getAvgMeasurementWithDetails(int avgId);


    @Transaction
    @Query("SELECT * FROM avg_measurements")
    LiveData<List<AvgMeasurementWithMeasurements>> getAvgDetails();

    @Transaction
    @Query("SELECT * FROM avg_measurements WHERE id = :avgId")
    AvgMeasurementWithMeasurements getAvgNotLiveDetails(int avgId);

    //查询条数
    @Query("SELECT COUNT(*) FROM avg_measurements")
    LiveData<Integer> getAvgMeasurementCount();

    //打印信息查询
    @Query("SELECT " +
            "a.id AS avg_id, " +
            "l.location_name AS area_name, " +
            "COUNT(m.id) AS point_count, " +
            "GROUP_CONCAT(m.id) AS measurement_ids " +
            "FROM avg_measurements a " +
            "JOIN locations l ON a.location_id = l.id " +
            "LEFT JOIN measurements m ON a.id = m.parent_id " +
            "WHERE a.id = :avgId " +
            "GROUP BY a.id, l.location_name")
    LiveData<MeasurementSummary> getMeasurementSummary(int avgId);
}
